package com.blog.util;

import com.blog.db.DataBase;
import com.blog.pojo.Column;
import com.blog.pojo.Table;
import com.blog.ui.DatabaseUtil;
import javafx.scene.control.Tab;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class DataBaseUtils {

    //获取到mysql中所有的数据库名称

    //获取数据库连接
    public static Connection getConnection(DataBase db) throws Exception {
        Properties props = new Properties();
        props.put("remarksReporting", "true");//获取数据库的备注信息
        props.put("user", db.getUserName());
        props.put("password", db.getPassWord());
        Class.forName(db.getDriver());//注册驱动
        return DriverManager.getConnection(db.getUrl(), props);
    }


    //获取数据库列表
    public static List<String> getSchemas(DataBase db) throws Exception {
        //1.获取元数据
        Connection connection = getConnection(db);
        DatabaseMetaData metaData = connection.getMetaData();
        //2.获取所有数据库列表
        ResultSet rs = metaData.getCatalogs();
        List<String> list = new ArrayList<String>();
        while (rs.next()) {
            list.add(rs.getString(1));
        }
        rs.close();
        connection.close();
        return list;
    }

    /**
     * 获取数据库中的表和字段构造实体类
     * Table对象
     * 1.参数  database 数据库对象
     * 2.操作步骤
     * 获取连接
     * 获取databasemetaData
     * 获取当前数据库中的所有表
     * 获取每个表中的所有字段
     * 封装到Java对象中即可
     */
    public static List<Table> getDbInfo(DataBase db) throws Exception {
        // 1.获取连接
        Connection connection = getConnection(db);
        // 2.获取元数据
        DatabaseMetaData metaData = connection.getMetaData();
        // 3.获取当前数据库中的所有表
        ResultSet tables = metaData.getTables(null, null, null, new String[]{"TABLE"});
        List<Table> list = new ArrayList<>();
        while (tables.next()) {
            Table table = new Table();
            // 表名
            String tableName = tables.getString("TABLE_NAME");
            // 类名
            String className = removePrefix(tableName);
            // 描述
            String remarks = tables.getString("REMARKS");
            // 主键
            ResultSet primaryKeys = metaData.getPrimaryKeys(null, null, tableName);
            String keys = "";
            while (primaryKeys.next()) {
                String keyname = primaryKeys.getString("COLUMN_NAME");
                keys += keyname + ",";
            }
            table.setName(tableName);
            table.setClassName(className);
            table.setComment(remarks);
            table.setKey(keys);
            // 处理表中的字段
            ResultSet columns = metaData.getColumns(null, null, tableName, null);
            List<Column> columnList = new ArrayList<>();
            while (columns.next()) {
                Column column = new Column();
                // 构造column对象
                // 列名称
                String columnName = columns.getString("COLUMN_NAME");
                column.setColumnName(columnName);
                // 属性名
                String attName = StringUtils.toJavaVariableName(columnName);
                column.setColumnClassName(attName);
                // Java类型和数据库类型
                String dbType = columns.getString("TYPE_NAME");
                String javaType = PropertiesUtils.customMap.get(dbType);
                column.setColumnDbType(javaType);
                // 备注
                String columnRemark = columns.getString("REMARKS");
                column.setColumnComment(columnRemark);
                // 是否主键
                String pri = null;
                if (StringUtils.contains(columnName , keys.split(","))) {
                    pri = "PRI";
                }
                column.setColumnKey(pri);
                columnList.add(column);
            }
            columns.close();
            table.setColumns(columnList);
            list.add(table);
        }
        tables.close();
        connection.close();
        return list;
    }

    public static String removePrefix(String tableName) {
        String prefixes = PropertiesUtils.customMap.get("tableRemovePrefixes");
        String temp = tableName;
        for (String pf : prefixes.split(",")) {
            temp = StringUtils.removePrefix(temp, pf, true);
        }
        // temp = user = User
        return StringUtils.makeAllWordFirstLetterUpperCase(temp);
    }

    public static void main(String[] args) throws Exception {
        DataBase db = new DataBase("MYSQL", "blog");
        db.setUserName("root");
        db.setPassWord("123456");
        List<Table> dbInfo = DataBaseUtils.getDbInfo(db);
        for (Table table : dbInfo) {
            System.out.println(table);
        }
    }
}
